<template>
  <section>
    <el-button @click="selectFile"> 导入 </el-button>
    <el-button @click="exportFile"> 导出 </el-button>
    <!-- <input
      type="file"
      id="file"
      style="display: none"
      accept=".xlsx"
      ref="input"
      @change="fileChange"
    /> -->
  </section>
</template>

<script>
import XLSX from "xlsx";
// import xlsxUtil from './processingXlsx'
let inputEl = document.createElement('input')
inputEl.setAttribute('type','file')
inputEl.setAttribute('accept','.xlsx')
export default {
  name: "XlsxDemo",
  created() {},
  mounted() {
    inputEl.addEventListener('change',this.fileChange)
  },
  methods: {
    // 导出
    exportFile() {
      let date = new Date();
      let month = date.getMonth() + 1;
      let D = date.getDate();
      let h = date.getHours();
      let m = date.getMinutes();
      let versionDate = `${JSON.stringify(date.getFullYear()).slice(2)}${
        month < 10 ? "0" + month : month
      }${D < 10 ? "0" + D : D}${h < 10 ? "0" + h : h}${m < 10 ? "0" + m : m}`;

      // let json = [
      //   {
      //     姓名: 1,
      //     性别: 2,
      //   },
      //   {
      //     姓名: ' ',
      //     年龄: ' ',
      //     性别: ' ',
      //   },
      // ];
      
      // let sheet = XLSX.utils.json_to_sheet(json);

      // let aoa = [
      //   ['标题',null,null,null],
      //   ['姓名', '性别', '年龄', '注册时间'],
      //   ['张三', null, 18, new Date()],
      //   ['李四', '女', null, new Date()]
      // ];

      let aoa = [
        ['标题',null,null,null],
        ['公司', null, '项目', '进度'],
        ['蓝斯', '1组' , '排班', '50%'],
        [null, null, '调度', '50%'],
        [null, '2组' , 'gis', '50%']
      ];

      let sheet = XLSX.utils.aoa_to_sheet(aoa);

      sheet['!merges'] = [
        // 设置A1-C1的单元格合并
        {s: {r: 0, c: 0}, e: {r: 0, c: 3}},
        {s: {r: 1, c: 0}, e: {r: 1, c: 1}},
        {s: {r: 2, c: 0}, e: {r: 4, c: 0}},
        {s: {r: 2, c: 1}, e: {r: 3, c: 1}}
      ];
      console.log(sheet)
      let blob = this.sheet2blob(sheet);

      this.openDownloadDialog(blob, `导出${versionDate}.xlsx`);
    },
    // 选择文件
    selectFile() {
      // this.$refs.input.click();
      console.log(inputEl)
      inputEl.click()
    },
    // selectFile2() {
    //   console.log(xlsxUtil.importFile)
    //   xlsxUtil.importFile('111')
    //     .then(res => {
    //       console.log(res)
    //     })
    //     .catch(e => {
    //       console.log(e)
    //     })
    // },
    // 文件上传后的回调
    fileChange(e) {
      let files = e.target.files;
      // console.log(files);
      if (files.length == 0) return;
      let f = files[0];
      if (!/\.xlsx$/g.test(f.name)) {
        alert("仅支持读取xlsx格式！");
        return;
      }
      this.readWorkbookFromLocalFile(f, (workbook) => {
        // this.readWorkbook(workbook);
        console.log("workbook:", workbook);
        let sheetNames = workbook.SheetNames; // 工作表名称集合
        let worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
        // let headers = this.get_header_row(worksheet);
        let json = XLSX.utils.sheet_to_json(worksheet,{range: 1,header: 1});
        // let obj = XLSX.utils.sheet_to_row_object_array(worksheet);
        // let aoa = XLSX.utils.sheet_to_aoa(worksheet);
        // let sheet = XLSX.utils.json_to_sheet(json);

        // let blob = this.sheet2blob(sheet);
        // this.openDownloadDialog(blob, '1111导出.xlsx');
        console.log("sheet_to_json:", json);
        json[1].forEach((el,i) => {
          console.log(el,i)

        });
        // console.log("sheet_to_row_object_array:", obj);
        console.log(XLSX.utils);
        // console.log("json_to_sheet:", sheet);
        // console.log("headers:", headers);
      });
    },
    // 读取本地excel文件
    readWorkbookFromLocalFile(file, callback) {
      let reader = new FileReader();
      reader.onload = function (e) {
        let data = e.target.result;
        let workbook = XLSX.read(data, { type: "binary",sheetRows: 0 });
        if (callback) callback(workbook);
      };
      reader.readAsBinaryString(file);
    },
    // 表头处理
    get_header_row(sheet) {
      const headers = [];
      /* sheet['!ref']表示所有单元格的范围，例如从A1到F8则记录为 A1:F8*/
      const range = XLSX.utils.decode_range(sheet["!ref"]);
      let C,
        R = range.s.r; /* 从第一行开始 */
      /* 按列进行数据遍历 */
      for (C = range.s.c; C <= range.e.c; ++C) {
        /* 查找第一行中的单元格 */
        const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];

        let hdr = "UNKNOWN " + C; // <-- 进行默认值设置
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);

        headers.push(hdr);
      }
      return headers;
    },
    // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
    sheet2blob(sheet, sheetName) {
      sheetName = sheetName || "sheet1";
      let workbook = {
        SheetNames: [sheetName],
        Sheets: {},
      };
      workbook.Sheets[sheetName] = sheet;
      // 生成excel的配置项
      let wopts = {
        bookType: "xlsx", // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: "binary",
      };
      let wbout = XLSX.write(workbook, wopts);
      let blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" });
      // 字符串转ArrayBuffer
      function s2ab(s) {
        let buf = new ArrayBuffer(s.length);
        let view = new Uint8Array(buf);
        for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
        return buf;
      }
      return blob;
    },
    /**
     * 通用的打开下载对话框方法，没有测试过具体兼容性
     * @param url 下载地址，也可以是一个blob对象，必选
     * @param saveName 保存文件名，可选
     */
    openDownloadDialog(url, saveName) {
      if (typeof url == "object" && url instanceof Blob) {
        url = URL.createObjectURL(url); // 创建blob地址
      }
      let aLink = document.createElement("a");
      aLink.href = url;
      aLink.download = saveName || ""; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
      let event;
      if (window.MouseEvent) event = new MouseEvent("click");
      else {
        event = document.createEvent("MouseEvents");
        event.initMouseEvent(
          "click",
          true,
          false,
          window,
          0,
          0,
          0,
          0,
          0,
          false,
          false,
          false,
          false,
          0,
          null
        );
      }
      aLink.dispatchEvent(event);
      aLink.remove()
    },
  },
};
</script>

<style lang="scss" scoped>
</style>